﻿// --------------------------------------------------------------------------------------------------------------------
// <copyright file="SqlServerManagement.cs" company="Microsoft">
//   Microsoft
// </copyright>
// <summary>
//   Provides access to Management functions of SQL Server
// </summary>
// Copyright (c) Microsoft Corporation. All rights reserved.
// 
// THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, 
// EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES 
// OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
// ----------------------------------------------------------------------------------
// The example companies, organizations, products, domain names,
// e-mail addresses, logos, people, places, and events depicted
// herein are fictitious.  No association with any real company,
// organization, product, domain name, email address, logo, person,
// places, or events is intended or should be inferred.
// --------------------------------------------------------------------------------------------------------------------

namespace MySemanticSearch.Web.DataAccess
{
    using System;
    using System.Collections;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Globalization;
    using System.Linq;
    using System.ServiceModel.DomainServices.Hosting;
    using System.ServiceModel.DomainServices.Server;
    using System.Web.Management;

    using Microsoft.SqlServer.Management.Smo;

    /// <summary>
    /// Provides access to Management functions of SQL Server
    /// </summary>
    [EnableClientAccess]
    public class SqlServerManagement : DomainService
    {
        /// <summary>
        /// Creates an object that connects to the SQL Server instance
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql Instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <returns>
        /// A Server instance
        /// </returns>
        public static Server ConnectServerInstance(string sqlInstance = "", string database = "")
        {
            try
            {
                var sqlServer = new Server(sqlInstance);
                sqlServer.ConnectionContext.LoginSecure = true;
                sqlServer.ConnectionContext.DatabaseName = database;
                sqlServer.ConnectionContext.Connect();
                return sqlServer;
            }
            catch
            {
                var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatSqlServerInstance, sqlInstance);
                var errorMessage = App_Data.ExceptionMessages.ConnectServerInstanceError;

                throw new SmoException(errorMessage + Environment.NewLine + errorInformation);
            }
        }

        /// <summary>
        /// Checks if the SQL Server instance is available and can connect to it
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql instance.
        /// </param>
        /// <returns>
        /// A boolean indicating whether the SQL Server Instance is available
        /// </returns>
        public string CheckServerConnection(string sqlInstance = "")
        {
            if (String.IsNullOrEmpty(sqlInstance))
            {
                sqlInstance = Environment.MachineName;
            }

            ConnectServerInstance(sqlInstance);
            return sqlInstance;
        }

        /// <summary>
        /// Retrieves all used databases from the service
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql Instance.
        /// </param>
        /// <param name="hasFileTable">
        /// The has File Table.
        /// </param>
        /// <returns>
        /// A dictionary with all databases
        /// </returns>
        public List<string> GetDatabaseNameList(string sqlInstance, bool hasFileTable)
        {
            if (string.IsNullOrEmpty(sqlInstance))
            {
                return new List<string>();
            }

            var sqlServer = ConnectServerInstance(sqlInstance);

            try
            {
                var filetableDbs = new List<string>();

                foreach (var database in sqlServer.Databases.Cast<Database>().Where(database => (!database.IsSystemObject && database.IsFullTextEnabled) && !String.IsNullOrEmpty(database.FilestreamDirectoryName)))
                {
                    if (!hasFileTable)
                    {
                        filetableDbs.Add(database.Name);
                    }
                    else if (this.CountFiletables(sqlInstance, database.Name) > 0)
                    {
                        filetableDbs.Add(database.Name);
                    }
                }

                return filetableDbs;
            }
            catch
            {
                throw new SmoException(App_Data.ExceptionMessages.GetDatabaseNameListError);
            }
        }

        /// <summary>
        /// Returns the subset of databases with filetables
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql Instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <returns>
        /// The filetable database list.
        /// </returns>
        public IEnumerable<string> GetFileTableList(string sqlInstance, string database)
        {
            var list = new List<string>();

            if (string.IsNullOrEmpty(database))
            {
                return list;
            }

            using (var connection = ConnectServerInstance(sqlInstance, database).ConnectionContext.SqlConnectionObject)
            {
                var query = String.Format(CultureInfo.InvariantCulture, App_Data.SqlQueries.ListFileTables);
                using (var command = new SqlCommand(query))
                {
                    try
                    {
                        command.Connection = connection;

                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                list.Add(reader.GetString(0));
                            }
                        }
                    }
                    catch
                    {
                        var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database);
                        var errorMessage = App_Data.ExceptionMessages.GetFiletableListError;

                        throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                    }
                }
            }

            return list;
        }

        /// <summary>
        /// Returns the subset of databases with filetables
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql Instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <returns>
        /// The filetable database list.
        /// </returns>
        public int CountFiletables(string sqlInstance, string database)
        {
            using (var connection = ConnectServerInstance(sqlInstance, database).ConnectionContext.SqlConnectionObject)
            {
                var query = String.Format(CultureInfo.InvariantCulture, App_Data.SqlQueries.CountFileTables);
                using (var command = new SqlCommand(query))
                {
                    try
                    {
                        command.Connection = connection;

                        return (int)command.ExecuteScalar();
                    }
                    catch (Exception)
                    {
                        var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database);
                        var errorMessage = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.CountFiletablesError);
                        throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                    }
                }
            }
        }

        /// <summary>
        /// Create a new File Table.
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql Instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="fileTableName">
        /// The filetable.
        /// </param>
        /// <param name="createDatabase">
        /// The new database.
        /// </param>
        /// <returns>
        /// True if the filetable was created succesfully, false otherwise.
        /// </returns>
        public bool CreateFileTable(string sqlInstance, string database, string fileTableName, bool createDatabase)
        {
            if (string.IsNullOrEmpty(sqlInstance) || string.IsNullOrEmpty(database) || string.IsNullOrEmpty(fileTableName))
            {
                return false;
            }

            var sqlServer = ConnectServerInstance(sqlInstance, String.Empty);

            var connection = sqlServer.ConnectionContext.SqlConnectionObject;
            using (var secondConn = new SqlConnection(connection.ConnectionString + ";async=true"))
            {
                secondConn.Open();

                var query = String.Format(CultureInfo.InvariantCulture, App_Data.SqlQueries.SetupSqlServer);
                using (var command = new SqlCommand(query, secondConn))
                {
                    try
                    {
                        command.BeginExecuteNonQuery();
                    }
                    catch
                    {
                        var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatSqlServerInstance, sqlInstance) + Environment.NewLine +
                            String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                            String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFileTable, fileTableName) + Environment.NewLine;
                        var errorMessage = App_Data.ExceptionMessages.SetupSqlServerError;

                        throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                    }
                }
            }

            Database db;
            if (createDatabase)
            {
                db = CreateDatabase(database, sqlServer);
            }
            else
            {
                db = sqlServer.Databases[database];
                if (db == null)
                {
                    var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database);
                    var errorMessage = App_Data.ExceptionMessages.DatabaseNotFoundError;

                    throw new SmoException(errorMessage + Environment.NewLine + errorInformation);
                }
            }

            FullTextCatalog fullTextCatalog;
            if (db.FullTextCatalogs.Contains(App_Data.FileTableElements.FullTextCatalogName))
            {
                fullTextCatalog = db.FullTextCatalogs[App_Data.FileTableElements.FullTextCatalogName];
            }
            else
            {
                try
                {
                    fullTextCatalog = new FullTextCatalog(db, App_Data.FileTableElements.FullTextCatalogName);
                    fullTextCatalog.Create();
                }
                catch
                {
                    var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                                           String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFileTable, fileTableName);
                    var errorMessage = App_Data.ExceptionMessages.FullTextCatalogCreationError;

                    throw new SmoException(errorMessage + Environment.NewLine + errorInformation);
                }
            }

            Table fileTable;
            try
            {
                fileTable = new Table(db, fileTableName) { IsFileTable = true };
                fileTable.Create();
            }
            catch
            {
                var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                        String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFileTable, fileTableName);
                var errorMessage = App_Data.ExceptionMessages.FileTableCreationError;

                throw new SmoException(errorMessage + Environment.NewLine + errorInformation);
            }

            Index fileTableIndex;
            try
            {
                fileTableIndex = new Index(fileTable, App_Data.FileTableElements.FileTableIndexPrefix + fileTableName) { IsUnique = true };
                var indexColumn = new IndexedColumn(fileTableIndex, App_Data.FileTableElements.FileTableIndexedColumn, false);
                fileTableIndex.IndexedColumns.Add(indexColumn);
                fileTableIndex.Create();
            }
            catch (Exception)
            {
                throw new SmoException(App_Data.ExceptionMessages.FileTableIndexCreationError);
            }

            var searchPropertyList = db.SearchPropertyLists[App_Data.FileTableElements.SearchPropertyListName] ?? CreateSearchPropertyList(db);

            try
            {
                var fullTextIndex = new FullTextIndex(fileTable)
                {
                    CatalogName = fullTextCatalog.Name,
                    UniqueIndexName = fileTableIndex.Name,
                    SearchPropertyListName = searchPropertyList.Name
                };
                var fullTextIndexColumn = new FullTextIndexColumn(
                fullTextIndex, App_Data.FileTableElements.FullTextIndexColumn)
                {
                    Language = App_Data.FileTableElements.FullTextIndexColumnLanguage,
                    StatisticalSemantics = 1,
                    TypeColumnName = App_Data.FileTableElements.FullTextIndexColumnType
                };

                fullTextIndex.IndexedColumns.Add(fullTextIndexColumn);
                fullTextIndex.Create();
            }
            catch
            {
                throw new SmoException(App_Data.ExceptionMessages.FullTextIndexCreationError);
            }

            return true;
        }

        /// <summary>
        /// Retrieves all search properties from the resources
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql Instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="fileTable">
        /// The file Table.
        /// </param>
        /// <returns>
        /// A list of search properties
        /// </returns>
        public IEnumerable<string> GetSearchProperties(string sqlInstance, string database, string fileTable)
        {
            var list = new List<string>();

            if (string.IsNullOrEmpty(sqlInstance) || string.IsNullOrEmpty(database) || string.IsNullOrEmpty(fileTable))
            {
                return list;
            }

            using (var connection = ConnectServerInstance(sqlInstance, database).ConnectionContext.SqlConnectionObject)
            {
                using (var command = new SqlCommand(App_Data.SqlQueries.GetFiletableSearchProperties))
                {
                    try
                    {
                        command.Parameters.AddWithValue("@Filetable", fileTable);
                        command.Connection = connection;

                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                list.Add(reader.GetString(0));
                            }
                        }
                    }
                    catch
                    {
                        var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                            String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFileTable, fileTable);
                        var errorMessage = App_Data.ExceptionMessages.GetSearchPropertiesError;

                        throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                    }
                }
            }

            return list;
        }

        /// <summary>
        /// Overrides default OnError behaviour when an exception occurs
        /// </summary>
        /// <param name="errorInfo">
        /// The error info.
        /// </param>
        protected override void OnError(DomainServiceErrorInfo errorInfo)
        {
            if (errorInfo == null)
            {
                return;
            }

            var serviceException = errorInfo.Error;
            if (serviceException == null)
            {
                return;
            }

            throw new DomainException(serviceException.Message);
        }

        /// <summary>
        /// Creates a new database that supports Filetables in the SQL Server instance
        /// </summary>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="server">
        /// The server.
        /// </param>
        /// <returns>
        /// A new database
        /// </returns>
        private static Database CreateDatabase(string database, Server server)
        {
            try
            {
                var sqlDataFolder = server.InstallDataDirectory + @"\DATA\";

                var mdfFile = sqlDataFolder + database + App_Data.DatabaseElements.mdfFileSuffix;
                var ldfFile = sqlDataFolder + database + App_Data.DatabaseElements.ldfFileSuffix;
                var filesDir = sqlDataFolder + database;

                var dataBase = new Database(server, database);
                dataBase.FileGroups.Add(new FileGroup(dataBase, App_Data.DatabaseElements.PrimaryFileGroupName));
                dataBase.FileGroups.Add(
                    new FileGroup(dataBase, App_Data.DatabaseElements.FilestreamFileGroupPrefix + dataBase, true));

                var primaryDataFile = new DataFile(
                    dataBase.FileGroups[App_Data.DatabaseElements.PrimaryFileGroupName],
                    App_Data.DatabaseElements.PrimaryFileGroupDatafile,
                    mdfFile)
                {
                    Size = Double.Parse(App_Data.DatabaseElements.PrimaryFileGroupDatafileSize),
                    GrowthType = FileGrowthType.KB,
                    Growth = Double.Parse(App_Data.DatabaseElements.PrimaryFileGroupDatafileGrowth)
                };

                var filestreamDataFile =
                    new DataFile(
                        dataBase.FileGroups[App_Data.DatabaseElements.FilestreamFileGroupPrefix + dataBase],
                        App_Data.DatabaseElements.FileStreamDataFilePrefix + dataBase,
                        filesDir);

                dataBase.FileGroups[App_Data.DatabaseElements.PrimaryFileGroupName].Files.Add(primaryDataFile);
                dataBase.FileGroups[App_Data.DatabaseElements.FilestreamFileGroupPrefix + dataBase].Files.Add(
                    filestreamDataFile);

                var logFile = new LogFile(dataBase, App_Data.DatabaseElements.LogFileName, ldfFile)
                {
                    Size = Double.Parse(App_Data.DatabaseElements.LogFileSize),
                    GrowthType = FileGrowthType.Percent,
                    Growth = Double.Parse(App_Data.DatabaseElements.LogFileGrowth)
                };
                dataBase.LogFiles.Add(logFile);

                dataBase.FilestreamNonTransactedAccess = FilestreamNonTransactedAccessType.Full;
                dataBase.FilestreamDirectoryName = System.IO.Path.GetRandomFileName();

                dataBase.Create();

                return dataBase;
            }
            catch
            {
                var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database);
                var errorMessage = App_Data.ExceptionMessages.CreateDatabaseError;

                throw new SmoException(errorMessage + Environment.NewLine + errorInformation);
            }
        }

        /// <summary>
        /// Creates a Search Property List in a database
        /// </summary>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <returns>
        /// The Search Property List object
        /// </returns>
        private static SearchPropertyList CreateSearchPropertyList(Database database)
        {
            try
            {
                var searchPropertyList = new SearchPropertyList(database, App_Data.FileTableElements.SearchPropertyListName);
                searchPropertyList.Create();

                var resourceSet = App_Data.SearchProperties.ResourceManager.GetResourceSet(CultureInfo.CurrentUICulture, true, true);
                foreach (DictionaryEntry entry in resourceSet)
                {
                    var name = (string)entry.Key;
                    var description = (string)entry.Value;
                    var guid = App_Data.SearchPropertiesValues.ResourceManager.GetString(name + "_GUID");
                    var id = Int32.Parse(App_Data.SearchPropertiesValues.ResourceManager.GetString(name + "_ID"), CultureInfo.InvariantCulture);

                    new SearchProperty(searchPropertyList, name, guid, id, description).Create();
                }

                searchPropertyList.Alter();

                return searchPropertyList;
            }
            catch
            {
                var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database);
                var errorMessage = App_Data.ExceptionMessages.CreateSearchPropertyListError;

                throw new SmoException(errorMessage + Environment.NewLine + errorInformation);
            }
        }
    }
}
